/*******************************************************************************
 * Copyright (c) 2010 liw.
 * All rights reserved. 
 * 
 * This file is part of VanBus.
 * 
 *     VanBus is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     VanBus is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 * 
 *     You should have received a copy of the GNU General Public License
 *     along with VanBus.  If not, see <http://www.gnu.org/licenses/>.
 * Contributors:
 *     liw - initial API and implementation
 ******************************************************************************/
package org.niclab.vanbus.database;

import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.niclab.vanbus.model.BusRoute;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

public class BusRouteDAO extends AbstractDAO{

	
	private static final String LOG_TAG="BusRouteDAO";
	
	public static final String DATABASE_TABLE_NAME = ApplicationDataBase.DATABASE_TABLE_ROUTE;
	
	public static final String[] TABLE_ROUTE_COLUMNS={"_id", "ROUTE_ID","NAME","DIR","DIR_DESP","FAVORITE"};
	
	public static final String[] TABLE_ROUTE_COLUMNS_DEFS={"INTEGER PRIMARY KEY ASC AUTOINCREMENT,",
		 "VARCHAR(255) NOT NULL,",
		 "VARCHAR(255),",
		 "VARCHAR(255) NOT NULL,",
		 "VARCHAR(255),",
		 "BOOLEAN DEFAULT 0", // 0 is false, 1 is true since sqlite doesn't really have boolean type
	};
	
	public static final String TABLE_ROUTE_CONSTRAINTS="CONSTRAINT uniqueIndex UNIQUE (ROUTE_ID,DIR)";
	
	
	
	
	public BusRouteDAO(SQLiteOpenHelper openHelper){
		super(openHelper);
					
	}
	
	public BusRoute getBusRoute(Cursor cursor){
		BusRoute route = new BusRoute();
		route.setDbId(cursor.getInt(cursor.getColumnIndex("_id")));
		route.setFavorite(cursor.getInt(cursor.getColumnIndex("FAVORITE"))==1? true:false);
		route.setRouteId(cursor.getString(cursor.getColumnIndex("ROUTE_ID")));
		route.setRouteName(cursor.getString(cursor.getColumnIndex("NAME")));
		route.setRouteDir(cursor.getString(cursor.getColumnIndex("DIR")));
		route.setRouteDirDesp(cursor.getString(cursor.getColumnIndex("DIR_DESP")));
		
		return route;
	}
	
	public Cursor getFavoriteRoutes(){
		
		Cursor cursor = null;
		

		cursor = getDB().query(DATABASE_TABLE_NAME, null,"FAVORITE = 1",null, null, null,null);

		return cursor;
	}
	
	
	public void checkFavoriteRoutes(List<BusRoute> routes){
		StringBuilder whereBuilder = new StringBuilder();
		whereBuilder.append("FAVORITE =1 AND trim(ROUTE_ID)||trim(DIR) IN (");

		Iterator<BusRoute> itr = routes.iterator();
		
		while(itr.hasNext()){

			BusRoute route = itr.next();
			whereBuilder.append("'");
			whereBuilder.append(route.getRouteId());
			whereBuilder.append(route.getRouteDir());
			whereBuilder.append("'");
			if(itr.hasNext())
				whereBuilder.append(",");
			else
				whereBuilder.append(")");


		}
		
		Log.d(LOG_TAG,"checkFavoriteRoutes: "+ whereBuilder.toString());
		SQLiteQueryBuilder qBuilder = new SQLiteQueryBuilder();
		

		qBuilder.appendWhere(whereBuilder.toString());
		qBuilder.setTables(DATABASE_TABLE_NAME);
			
		Cursor cursor = qBuilder.query(getDB(),null,null,null,null, null, null);

		Log.d(LOG_TAG,"checkFavorite: result size: "+ cursor.getCount());
		
		if(cursor.moveToFirst()){
			do{
				
				int dbId = cursor.getInt(0);
				String routeId = cursor.getString(1);
				String dir = cursor.getString(3);
				Log.v(LOG_TAG, "checkFavoriteRoutes: routeId: "+ routeId+" Dir: "+ dir);
				for(BusRoute route:routes){
					
					if(route.getRouteId().equals(routeId) && route.getRouteDir().equalsIgnoreCase(dir)){
						
						route.setDbId(dbId);
						route.setFavorite(true);
					}
				}
				
				
			}while(cursor.moveToNext());
		}
		
		
		cursor.close();

		
		
	}

	
	public Cursor getSortedFavoriteRoutes(){
		Cursor cursor = null;
		cursor = getDB().query(DATABASE_TABLE_NAME,null,"FAVORITE=1",null,null,null,"ROUTE_ID");
		return cursor;
	}
	
	public void setFavorite(BusRoute route){
		
				
		getDB().beginTransaction();
		
		try{			
			ContentValues values = new ContentValues();
			values.put("FAVORITE", true);
			
			String[] args = new String[2];
			args[0] = route.getRouteDir();
			args[1] = route.getRouteId();
			int rowUpdated = getDB().update(DATABASE_TABLE_NAME, values, "DIR=? AND ROUTE_ID=?", args);
			
			if(rowUpdated==0) {
				ContentValues insertRec = new ContentValues();
				insertRec.put("ROUTE_ID", route.getRouteId());
				insertRec.put("NAME", route.getRouteName());
				insertRec.put("DIR", route.getRouteDir());
				insertRec.put("DIR_DESP", route.getRouteDirDesp());
				insertRec.put("FAVORITE",true);
				getDB().insert(DATABASE_TABLE_NAME, "ROUTE_ID", insertRec);
			}
			
			route.setFavorite(true);
			getDB().setTransactionSuccessful();
		}finally{
			getDB().endTransaction();
		
		}
		
		
		
	}
	
		
	public void unsetFavorite(BusRoute route){
		
		ContentValues values = new ContentValues();
		values.put("FAVORITE", false);

		if(route.getDbId()==0){
			getDB().update(DATABASE_TABLE_NAME, values, "ROUTE_ID="+route.getRouteId()+"AND DIR="+route.getRouteDir(), null);				
		}else{	
			getDB().update(DATABASE_TABLE_NAME, values, "_id="+route.getDbId(), null);
		}			

	}
	
	public int insertBusRoute(BusRoute route){
		ContentValues values = new ContentValues();
		values.put("ROUTE_ID", route.getRouteId());
		values.put("NAME", route.getRouteName());
		values.put("DIR", route.getRouteDir());
		values.put("DIR_DESP", route.getRouteDirDesp());
		values.put("FAVORITE", false);
		return (int) getDB().insert(DATABASE_TABLE_NAME, "NAME", values);

	}
	
	public void removeBusRoute(BusRoute route){
	
		if(route.getDbId() !=0 )
			getDB().delete(DATABASE_TABLE_NAME, "_id="+route.getDbId(), null);
		else
			getDB().delete(DATABASE_TABLE_NAME, "ROUTE_ID="+route.getRouteId()+"AND DIR="+route.getRouteDir(), null);

		
	}


	public int getRouteDbId(BusRoute route) {
		
		if(route.getDbId()>0)
			return route.getDbId();
		
		String[] args = new String[]{route.getRouteId(),route.getRouteDir()};
		Cursor cursor = getDB().query(DATABASE_TABLE_NAME, new String[]{"_id"}, "ROUTE_ID= ? AND DIR = ?", args, null, null, null);
		try{
		if(cursor.moveToFirst())
			return cursor.getInt(0);
	
		else 
			return -1;
		}finally{
			cursor.close();
		}
	}
	


}
